package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;



import crm.dbcoon.DBConnect;
import crm.model.Linkman;
import crm.model.Notice;
import crm.model.Salearea;
import crm.util.Pagination;

public class SaleareaControl {
    public static Pagination pageInstance = null;
	//获取area_name List
    public static List getArea_nameList(){
    	DBConnect dbc  = new DBConnect();
    	String sql = "select * from param_area";
    	List list = new ArrayList();
    	ResultSet rs = null;
    	dbc.prepareStatement(sql);
    	try {
			rs = dbc.executeQuery();
			while(rs.next()){
				list.add(rs.getString("area_name"));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
    }
    public static int getAreaId(String areaName){
    	DBConnect dbc  = new DBConnect();
    	String sql = "select * from param_area where area_name = ?";
    	ResultSet rs = null;
    	dbc.prepareStatement(sql);
    	try {
			rs = dbc.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}finally{
			dbc.close();
		}
		return 0;
    }
/*    public static void main(){
    	List list = getArea_nameList();
    	for(int i = 0 ;  i<list.size(); i++){
    		System.out.println(list.get(i));
    	}
    }*/
    //获取一条动态
    public Salearea getOneSaleareaById(int id){
 	   DBConnect dbc = new DBConnect();
 	   String sql = "select * from param_area where id = "+id;
 	   Salearea notice = new Salearea();
 	   dbc.prepareStatement(sql);
 	   try{
 		   ResultSet rs = dbc.executeQuery();
 		   while(rs.next()){
 			  notice.setId(id);
 			  notice.setArea_name(rs.getString("area_name"));
 			  notice.setSale_target(rs.getString("sale_target"));
 			  notice.setCompetitor(rs.getString("competitor"));
 			      
 		   }
 	   }
 		   catch(SQLException e){
 			   e.printStackTrace();
 		   }
 		   finally{
 			   dbc.close();
 		   }
 		   return notice;
 	   }
    
	// 增加销售区域
	public boolean insertSalearea(Salearea salearea) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into param_area(area_name,sale_target,competitor)values(?,?,?)";
		dbc.prepareStatement(sql);
		try {
			
			dbc.setString(1, salearea.getArea_name());
			dbc.setString(2, salearea.getSale_target());
			dbc.setString(3, salearea.getCompetitor());
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}

	// 根据id删除销售区域
	public boolean deleteSalearea(int id) {
		DBConnect dbc = new DBConnect();
		String sql = "delete from param_area where id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}

	// 根据id修改销售区域
	public boolean updateSalearea(Salearea salearea, int id) {
		DBConnect dbc = new DBConnect();
		String sql = "update param_area set area_name='" + salearea.getArea_name()
				+ "',sale_target='" + salearea.getSale_target() + "',competitor='" 
				+ salearea.getCompetitor() + "'where id=" + id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	// customer_add.jsp需要调用该方法 
	public static Collection<Salearea> getAllSalearea() {
		DBConnect dbc = new DBConnect();
		ResultSet rs = null;
		Collection areas = new ArrayList<Salearea>();
		String sql = "select id,area_name from param_area";
		dbc.prepareStatement(sql);
		try {
			rs = dbc.executeQuery();
			while (rs.next()) {
				Salearea area = new Salearea();
				area.setId(rs.getInt(1));
				area.setArea_name(rs.getString(2));
				areas.add(area);
			}
			System.out.println(areas.size());
		} catch (SQLException e) {
			System.out.println("select fail！\n" + e.getMessage());
			return null;
		} finally {
			dbc.close();
		}
		return areas;
	}
	
	public static String getAreaName(int id){
		DBConnect dbc = new DBConnect();
		String sql = "SELECT * FROM param_area WHERE id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			return rs.getString("area_name");
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	public static HashMap getAllSaleArea() {
	     DBConnect dbConnect =new DBConnect();
	     ResultSet rs = null;
	     HashMap areas = new HashMap();
	     String sql = "select * from param_area";
	     dbConnect.prepareStatement(sql);
	     try {
			rs = dbConnect.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String area_name = rs.getString("area_name");
				areas.put(id, area_name);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			dbConnect.close();
		}
		return areas;
	}
	
	 public static ArrayList getAllSaleareas() {
			
			String sql = "select * from param_area order by id";
			DBConnect dbc = new DBConnect();
			ResultSet rs = null;
			ArrayList<Salearea> saleareas=new ArrayList();
			dbc.prepareStatement(sql);
			try {						
				rs = dbc.executeQuery();
				while(rs.next())
				{
					Salearea salearea=new Salearea();
					salearea.setId(rs.getInt("id"));
					salearea.setArea_name(rs.getString("area_name"));
					salearea.setSale_target(rs.getString("sale_target"));
					salearea.setCompetitor(rs.getString("competitor"));
					saleareas.add(salearea);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// ������һ��Ҫע�������ݿ�����
				dbc.close(rs);
				dbc.close();
			}
			return saleareas;
		}
	
	// 分页显示
	public Collection GetSalearea(int page, int rowsPerPage, String addtime)
			throws SQLException, Exception {
		String sql = "SELECT * FROM param_area order by " + addtime + " desc";
		pageInstance = new SaleareaControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}

	class SaleareaControlPage extends Pagination {
		public SaleareaControlPage(String sql, int rowsPerPage) {
			super(sql, rowsPerPage, 1);
		}

		protected Collection<Salearea> packResultSet(ResultSet rs)
				throws SQLException {
			Collection<Salearea> SaleareaList = new ArrayList<Salearea>();
			while (rs.next()) {
				Salearea s = new Salearea();
				s.setId(rs.getInt("id"));
				s.setArea_name(rs.getString("area_name"));
				s.setSale_target(rs.getString("sale_target"));
				s.setCompetitor(rs.getString("competitor"));
				SaleareaList.add(s);
				s = null;
			}
			return SaleareaList;
		}
	}
	
}
